#!/bin/bash
########################################################################
# script name:  list_host.sh
# Used for:
# Author:       Martin Dumont
# Contributors:
# Version:      0.1
#
# (date format: date +'%Y/%m/%d')
#
# Date          Version Contributors    Modifications
# ----------    ------- ------------    -------------
# 2012/05/30    0.1     MDUMONT         initial
########################################################################
usage()
{
cat << STOP

usage: $0 [options]

This script is used for ....

OPTIONS:
   -q               Query: columns values to query
   -c               Filter columns to display
   -s               Script output (without separators)

Available columns:
${COLUMNS[@]}

Ex.:
$0 -q ${COLUMNS[0]}=saprmt02
$0 -q ${COLUMNS[1]}=aix
$0 -q ${COLUMNS[1]}=aix,${COLUMNS[7]}=y,${COLUMNS[2]}=!prd
$0 -q ${COLUMNS[1]}=!aix,${COLUMNS[2]}=prd -c ${COLUMNS[0]}
$0 -q ${COLUMNS[7]}=n -c ${COLUMNS[5]}
STOP
}
#######################################
# Get parameters
#######################################
while getopts q:c:s OPTION
do
	case $OPTION in
		q)
			WHERE=( $(echo $OPTARG | tr '[A-Z]' '[a-z]' | sed 's/,/ /g') )
		;;
		c)
			SELECT=$(echo $OPTARG | tr '[A-Z]' '[a-z]')
		;;
		s)
			QOPTION="-Bse"
		;;
		\?)
			usage
			exit 1
		;;
		*)
			usage
			exit 1
		;;
esac
done
#######################################
# Variables and environnement
#######################################
command -v /usr/bin/mysql/mysql > /dev/null 2>&1 && MYSQL=/usr/bin/mysql/mysql
command -v /opt/freeware/bin/mysql/mysql > /dev/null 2>&1 && MYSQL=/opt/freeware/bin/mysql/mysql
command -v /usr/bin/mysql > /dev/null 2>&1 && MYSQL=/usr/bin/mysql
if [ -z $MYSQL ]; then echo "Need mysql client installed"; exit 1; fi
if [ -z $QOPTION ]; then QOPTION="-e"; else QOPTION="-Bse"; fi
MYSQL_COMMAND="sudo -u saunix $MYSQL -hsaplxs01 -uguest -pguest opmgt"
COLUMNS=( $($MYSQL_COMMAND -Bse "SHOW COLUMNS FROM hostconfig" | awk '{print $1}') )
if [ $OPTIND = 1 ]; then usage; exit 1; fi
#######################################
# MAIN
#######################################
firsttime=y
for element in ${WHERE[@]}
do
	e_COLUMN=$(echo $element | cut -d= -f1)
	e_VALUE=$(echo $element | cut -d= -f2)
	for i in ${COLUMNS[@]}
	do
		if [ "$e_COLUMN" = "$i" ]
		then
			if [ "${e_VALUE:0:1}" = "!" ]; then NOT=NOT; e_VALUE=$(echo $e_VALUE | cut -c2-); else NOT=""; fi
			if [ "$e_VALUE" = "all" ] && [ "$i" = "${COLUMNS[0]}" ]; then e_VALUE="%"; fi
			if [ "$firsttime" = "y" ]
			then
				WHERE_CLAUSE="WHERE $e_COLUMN $NOT LIKE '%$e_VALUE%' "
				firsttime=n
			else
				WHERE_CLAUSE="$WHERE_CLAUSE AND $e_COLUMN $NOT LIKE '%$e_VALUE%' "
			fi
		fi
	done
done
if [ -z "$SELECT" ]; then SELECT=${COLUMNS[0]}; fi
if [ "$SELECT" = "all" ]; then SELECT=\*; fi
$MYSQL_COMMAND $QOPTION "SELECT SQL_CACHE DISTINCT $SELECT FROM hostconfig $WHERE_CLAUSE"
#######################################
exit 0

